Imports System.Data.Common
Partial Class Presentation_Management_Reporting_Statistics
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim Sql As String
        Dim Records As DbDataReader
        Dim Params(0) As GenParameter
        Dim CountSql As String

        Page.Title = "Persons's Reports"

        If Not Page.IsPostBack Then

            'Populate the Executives List
            dlExecutives.DataTextField = "ExecutiveName"
            dlExecutives.DataValueField = "ExecutiveID"

            If Not User.IsInRole("Top Management") Then

                Sql = "SELECT ExecutiveID, ExecutiveName FROM ExecutiveMaster" & _
                " WHERE Active = 1 AND DepartmentID = 2  AND Location = ?Location ORDER BY ExecutiveName"

            Else
                Sql = "SELECT ExecutiveID, ExecutiveName FROM ExecutiveMaster" & _
                    " WHERE Active = 1 AND DepartmentID = 2 ORDER BY ExecutiveName"

            End If

            

            Params(0) = New GenParameter("?Location", Request.Cookies("Profile").Item("Location"), Data.DbType.String)

            Records = GenHelper.ExecuteReader(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())
            dlExecutives.DataSource = Records
            dlExecutives.DataBind()
            Records.Close()

        End If



        Sql = "Select ExecutiveName, YEAR (WorkflowMaster.MeetingDate) AS 'Year', MonthName(WorkflowMaster.MeetingDate) AS 'Month', count(distinct workflowMaster.MeetingDate) AS 'Days Reported' FROM ExecutiveMaster INNER JOIN WorkflowMaster ON ExecutiveMaster.ExecutiveID = WorkflowMaster.ExecutiveID" & _
        " WHERE WorkflowMaster.ExecutiveID = ?ExecutiveID" & _
        " GROUP BY ExecutiveName, Year(WorkflowMaster.MeetingDate), Month" & _
        " ORDER BY ExecutiveName, WorkflowMaster.MeetingDate desc"

        CountSql = "SELECT COUNT(*) FROM (Select ExecutiveName, YEAR (WorkflowMaster.MeetingDate), MonthName(WorkflowMaster.MeetingDate) AS 'Month', count(distinct workflowMaster.MeetingDate) FROM ExecutiveMaster INNER JOIN WorkflowMaster ON ExecutiveMaster.ExecutiveID = WorkflowMaster.ExecutiveID" & _
        " WHERE WorkflowMaster.ExecutiveID = ?ExecutiveID" & _
        " GROUP BY ExecutiveName, Year(WorkflowMaster.MeetingDate), Month" & _
        " ORDER BY ExecutiveName, Month(WorkflowMaster.MeetingDate)) AS test"

        Params(0) = New GenParameter("?ExecutiveID", dlExecutives.SelectedValue, Data.DbType.String)

        cstmReporting.Sql = Sql
        cstmReporting.CountSql = CountSql
        cstmReporting.Params = Params
        cstmReporting.ShowButton = True

    End Sub
End Class
